package com.pudding.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.pudding.entity.po.TbExcel;
import com.pudding.entity.vo.ExcelVO;
import com.pudding.mapper.ExcelMapper;
import com.pudding.service.ExcelWriteService;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

@Service
@Slf4j
@RequiredArgsConstructor
public class ExcelWriteServiceImpl implements ExcelWriteService {

    private final ExcelMapper excelMapper;

    /**
     * 写出数据到本地磁盘
     *
     * @param response
     */
    @Override
    public void demo1(HttpServletResponse response) {
        // 1. 查询数据库所有的数据
        List<TbExcel> tbExcels = excelMapper.selectList(null);
        // 将TbExcel转换为ExcelVO
        List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

        // 2. 方式一：写出数据到本地磁盘,默认写到第一页0
        //EasyExcel.write("E:\\test\\demo1.xlsx", ExcelVO.class)
        //        .sheet("sheet1")
        //        .doWrite(excelVOS);

        // 2. 方式二：提供给前端下载，需要使用到HttpServletResponse对象
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setContentType("application/octet-stream;charset=UTF-8"); //浏览器强制调起下载
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    //3. 通过设置转换器实现，将Long转为String导出，来解决精度丢失问题
                    .registerConverter(new LongStringConverter())
                    .sheet("模板")
                    .doWrite(excelVOS);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 单个sheet页写入多次，数据在多个集合
     * 注意：只能构建一个WriteSheet对象
     *
     * @param response
     */
    @Override
    public void demo2(HttpServletResponse response) {
        //1. 查数据，再转换为VO对象
        List<TbExcel> tbExcels = excelMapper.selectList(null);
        List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

        //2. 通过build来构建ExcelWriter写对象
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExcelVO.class).build()) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 通过build来构建writeSheet对象，这里注意 如果同一个sheet只要创建一次，一个sheet对象对应一个sheet页
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
            for (int i = 0; i < 5; i++) {
                excelWriter.write(excelVOS, writeSheet); //调用write()方法，往writeSheet页面写入excelVOS数据
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 将数据写入到多个sheet页中
     * 案例：将数据按照日期分组，每一组数据写到一个sheet页中
     *
     * @param response
     */
    @Override
    public void demo3(HttpServletResponse response) {
        //1. 查询数据并且进行转化为VO对象
        List<TbExcel> tbExcels = excelMapper.selectList(null);
        List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExcelVO.class).build()) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 将LocalDateTime转换为LocalDate，再进行分组
            Map<LocalDate, List<ExcelVO>> collect = excelVOS.stream()
                    .collect(Collectors.groupingBy(vo -> vo.getDatetimeCol().toLocalDate()));

            // 根据LocalDate对分组结果进行排序
            List<Map.Entry<LocalDate, List<ExcelVO>>> sortedEntries = collect.entrySet().stream()
                    .sorted(Map.Entry.comparingByKey())
                    .collect(Collectors.toList());

            int index = 0;
            for (LocalDate localDate : collect.keySet()) {
                WriteSheet writeSheet = EasyExcel.writerSheet(index, localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                excelWriter.write(collect.get(localDate), writeSheet);
                index++;
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 自定义样式：可以将样式的定义单独的抽取出来
     *
     * @param response
     */
    @Override
    public void demo4(HttpServletResponse response) {
        // 1. 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 20);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 2. 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 3. 自定义样式策略：这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 4. 查询数据并且转换为VO对象
        List<TbExcel> tbExcels = excelMapper.selectList(null);
        List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

        // 5. 写出数据到excel中
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    .registerWriteHandler(horizontalCellStyleStrategy)  //把我们的样式注册到EasyExcel中
                    .sheet("模板")
                    .doWrite(excelVOS);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 合并单元格，什么样的单元格可以合并？
     * 数据相同才可以合并单元格，比如时间相同可以合并，id列都不一样，就无法合并
     * 方式一：通过实体类上面的注解@ContentLoopMerge(eachRow = 2)方式进行合并
     *
     * @param response
     */
    @Override
    public void demo5(HttpServletResponse response) {
        // 1. 自定义数据，这里就没有从数据库中查询了
        List<ExcelVO> excelVOS = new ArrayList<>();
        excelVOS.add(new ExcelVO(1L, "字符串1", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(1L, "字符串2", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L, "字符串3", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L, "字符串4", BigDecimal.ONE, LocalDateTime.now()));

        // 2. 写出数据到excel中
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    .sheet("模板")
                    .doWrite(excelVOS);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 合并单元格，什么样的单元格可以合并？
     * 数据相同才可以合并单元格，比如时间相同可以合并，id列都不一样，就无法合并
     * 方式二：无需使用实体类上面的合并注解，直接自定义合并规则
     *
     * @param response
     */
    @Override
    public void demo6(HttpServletResponse response) {
        // 1. 自定义数据，这里就没有从数据库中查询了
        List<ExcelVO> excelVOS = new ArrayList<>();
        excelVOS.add(new ExcelVO(1L, "字符串1", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(1L, "字符串2", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L, "字符串3", BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L, "字符串4", BigDecimal.ONE, LocalDateTime.now()));

        // 相加
        BigDecimal sum = excelVOS.stream().map(ExcelVO::getDecCol).reduce(BigDecimal.ZERO, BigDecimal::add);
        // 2. 将合计追加到集合中（本质也是添加一个excelVOS对象，将id和时间设置为null）
        excelVOS.add(new ExcelVO(null, "合计", sum, null));

        // 3. 写出数据到excel中
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 4. 设置合并规则
            LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);

            EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    .sheet("模板")
                    .registerWriteHandler(loopMergeStrategy)
                    .doWrite(excelVOS);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 使用线程池，将数据写入到excel中不同的sheet页
     *
     * @param response
     */
    @Override
    public void exportData(HttpServletResponse response) {
        long start1 = System.currentTimeMillis();
        // 每次查询1万条，考虑性能，内存
        Integer pageSize = 10000;
        // 线程池大小跟cpu有关，一般是cpu数量 * 2 + 1
        Integer poolSize = 10;
        // 随机文件名
        String fileName = String.valueOf(UUID.randomUUID());
        // 查询数据总数
        Long totalCount = excelMapper.selectCount(null);
        if (totalCount == 0) {
            log.info("没有数据需要导出");
            return; // 如果没有数据，直接返回
        }

        int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
        // 设置计数器计数来判断线程是否执行完
        final CountDownLatch latch = new CountDownLatch(loopCount);
        log.info("要查询的次数===>{}", loopCount);

        // 创建一个线程池
        ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();  //为了写出到前端
            // 创建写对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 0; i < loopCount; i++) {
                final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码

                executorService.execute(() -> {
                    long start = System.currentTimeMillis();
                    // 分页查询数据（注意mybatis-plus分页查询要生效，必须在配置类中启用PaginationInterceptor）
                    IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                    List<TbExcel> records = data.getRecords(); // 取出查询的这一页的数据
                    log.info("第{}页,查询耗时===>{}", pageNum, System.currentTimeMillis() - start);

                    // 创建Sheet对象
                    WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, "第" + pageNum + "页")
                            .head(ExcelVO.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                            .build();
                    // 开始写数据库，加锁，锁对象要用唯一的，因为同一个excel文件不能同时进行写操作
                    synchronized (excelWriter) {
                        long start2 = System.currentTimeMillis();
                        excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                        log.info("数据写出耗时===》{}", System.currentTimeMillis() - start2);
                    }

                    latch.countDown(); //计数器减一
                });
            }
            latch.await();  //主线程等待子线程执行完
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
            executorService.shutdown(); // 关闭线程池
            outputStream.close();
            log.info("总耗时====》{}", System.currentTimeMillis() - start1);
        } catch (IOException | InterruptedException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 使用虚拟线程，将数据写入到excel中不同的sheet页中
     *
     * @param response
     */
    @Override
    public void exportData2(HttpServletResponse response) {
        long start1 = System.currentTimeMillis();
        Integer pageSize = 100000;
        Integer poolSize = 10;
        String fileName = String.valueOf(UUID.randomUUID());
        // 查询数据总数
        Long totalCount = excelMapper.selectCount(null);
        if (totalCount == 0) {
            log.info("没有数据需要导出");
            return; // 如果没有数据，直接返回
        }

        int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
        // 设置计数器计数来判断线程是否执行完
        final CountDownLatch latch = new CountDownLatch(loopCount);
        log.info("要查询的次数===>{}", loopCount);

        ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            // 创建写对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 0; i < loopCount; i++) {
                final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码
                Thread.ofVirtual().start(() -> {
                    long start = System.currentTimeMillis();
                    // 查询数据
                    IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                    List<TbExcel> records = data.getRecords();
                    log.info("第{}页,查询耗时===>{}", pageNum, System.currentTimeMillis() - start);

                    WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, "第" + pageNum + "页")
                            .head(ExcelVO.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                            .build();
                    synchronized (excelWriter) {
                        long start2 = System.currentTimeMillis();
                        excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                        log.info("数据写出耗时===》{}", System.currentTimeMillis() - start2);
                    }

                    latch.countDown();
                });
            }
            latch.await();
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
            executorService.shutdown(); // 关闭线程池
            outputStream.close();
            log.info("总耗时====》{}", System.currentTimeMillis() - start1);
        } catch (IOException | InterruptedException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 写入到一个sheet页中，单sheet页超过100万条数据就写不进去了
     *
     * @param response
     */
    @Override
    public void exportData3(HttpServletResponse response) {
        long start1 = System.currentTimeMillis();
        Integer pageSize = 100000;
        Integer poolSize = 10;
        String fileName = String.valueOf(UUID.randomUUID());

        // 查询数据总数
        Long totalCount = excelMapper.selectCount(null);
        if (totalCount == 0) {
            log.info("没有数据需要导出");
            return; // 如果没有数据，直接返回
        }

        int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
        // 设置计数器计数来判断线程是否执行完
        final CountDownLatch latch = new CountDownLatch(loopCount);
        log.info("要查询的次数===>{}", loopCount);

        ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            // 创建写对象，再循环外面只创建一个sheet
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(1, "第" + 1 + "页")
                    .head(ExcelVO.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerConverter(new LongStringConverter())
                    .build();
            for (int i = 0; i < loopCount; i++) {
                final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码
                Thread.ofVirtual().start(() -> {
                    long start = System.currentTimeMillis();
                    // 查询数据
                    IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                    List<TbExcel> records = data.getRecords();
                    log.info("第{}页,查询耗时===>{}", pageNum, System.currentTimeMillis() - start);

                    synchronized (excelWriter) {
                        long start2 = System.currentTimeMillis();
                        excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                        log.info("数据写出耗时===》{}", System.currentTimeMillis() - start2);
                    }
                    latch.countDown();
                });
            }
            latch.await();
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
            executorService.shutdown(); // 关闭线程池
            outputStream.close();
            log.info("总耗时====》{}", System.currentTimeMillis() - start1);
        } catch (IOException | InterruptedException e) {
            throw new RuntimeException(e);
        }
    }
}
